<?php
/**
 * Created by ephp
 * User: 22071
 * Date: 2019/6/14
 * Email: <zhendongdong@foxmail.com>
 */

namespace paper\pdo;

use paper\App;
use paper\database\Collection;
use paper\Paginate;
use paper\Relation;
use paper\support\Model;
use PDOException;

/**
 * @property Model $model
 * @property array insertData
 *
 */
class Query
{

    private static $pdo;
    private $config = [];
    private $table;
    private $where = [];
    private $where_str = '';
    private $limit;
    private $having;
    private $order;
    private $sql;
    private $bind_data = [];
    private $update_data = [];
    private $join_option = [];
    private $join_str = "";
    private $field = " * ";
    private $lock = '';
    private $alias = '';
    private $model = null;
    //    private $paginate = null;
    private $log = null;
    private $bind_index = 0;
    private $has_where = false;
    public $with = []; //预查询字段，只能在模型中使用
    private $index; //索引字段
    private $as_array = false; //索引字段

    //数据库字段
    const UPDATED_AT_FIELD = 'updated_at';
    const CREATED_AT_FIELD = 'created_at';


    /**
     * @var array
     */
    private $incField = [];
    /**
     * @var array
     */
    private $columns;
    /**
     * @var string
     */
    private $group = '';

    /**
     * Db constructor.
     * @param $config
     * @param string $table
     * @param string $prefix
     */
    public function __construct($config, $table = null, $prefix = null)
    {
        $this->config = $config;
        $this->log = App::getInstance()->log;

        if (!self::$pdo) {
            self::$pdo = new Mysql(
                $this->config["hostname"],
                $this->config["port"],
                $this->config["database"],
                $this->config["charset"],
                $this->config["username"],
                $this->config["password"]
            );
        }
        if ($table) {
            $this->table = $this->parseTable($table, $prefix);
        }

    }

    /**
     * @param array|string $attr
     * @return Query
     */
    public function with($attr = null)
    {
        if (!is_array($attr)) {
            $this->with[] = $attr;
        } else {
            $this->with = $attr;
        }
        return $this;
    }

    public function asArray()
    {
        $this->as_array = true;
    }

    public function model(Model $model)
    {
        $this->model = $model;
        $this->table($model->tabName());
        return $this;
    }

    public function index($filed = 'id')
    {
        $this->index = $filed;
        return $this;
    }


    /**
     * 获取编号
     * @return int
     */
    public function getBindIndex(): int
    {
        return $this->bind_index;
    }

    /**
     * 设置参数绑定占位符起始编号
     * 用于子查询 避免冲突
     * @param int $bind_index
     */
    public function setBindIndex(int $bind_index): void
    {
        $this->bind_index = $bind_index;
    }

    /**
     * 获取参数绑定的占位符
     * @return string
     */
    private function getBindField()
    {
        return ':bind_' . $this->bind_index++;
    }

    /**
     * 开启事务
     */
    public function startTrans()
    {
        self::$pdo->pdo()->beginTransaction();
    }


    /**
     * 回滚事务
     */
    public function rollback()
    {
        self::$pdo->pdo()->rollBack();
    }

    /**
     * 提交事务
     */
    public function commit()
    {
        self::$pdo->pdo()->commit();
    }

    /**
     * 锁定数据 （添加FOR UPDATE）
     * @param bool $lock
     * @return $this
     */
    public function lock($lock = false)
    {
        if ($lock) {
            $this->lock = ' FOR UPDATE';
        } else {
            $this->lock = '';
        }
        return $this;
    }

    /**
     * @return array
     */
    public function getBindData(): array
    {
        return $this->bind_data;
    }


    public function getSql(): string
    {
        return $this->sql;
    }

    public function alias($name): Query
    {
        $this->alias = $name;
        return $this;
    }


    public function table($table): Query
    {
        $this->table = $this->parseTable($table, null);
        return $this;
    }

    public function name($table, $prefix = null): Query
    {
        $this->table = $this->parseTable($table, $prefix);
        return $this;
    }

    private function parseTable($name, $prefix = null): string
    {
        return ($prefix ?: $this->config['prefix']) . $name;
    }


    public function limit($offset, int $limit = NULL)
    {
        if ($offset === false) {
            $this->limit = "";
        } else {
            if ($limit) {
                $this->limit = " LIMIT " . $offset . "," . $limit;
            } else {
                $this->limit = " LIMIT " . $offset;
            }
        }
        return $this;
    }

    /**
     * 设置排序
     * @param $str
     * @return $this
     */
    public function order($str)
    {
        if ($str != NULL) {
            $this->order = " ORDER BY {$str} ";
        }
        return $this;
    }

    /**
     * 设置排序
     * @param $str
     * @return $this
     */
    public function group($str)
    {
        if ($str != NULL) {
            $this->group = " GROUP BY {$str} ";
        }
        return $this;
    }

    /**
     * 设置自增
     * @param $field
     * @param int $value
     */
    public function inc($field, $value = 1)
    {
        $field_key = $this->getBindField();
        $this->incField[] = "`{$field}` = `$field` + $field_key";

        $this->bind_data[$field_key] = $value;
    }

    /**
     * 设置自减
     * @param $field
     * @param int $value
     */
    public function dec($field, $value = 1)
    {
        $field_key = $this->getBindField();
        $this->incField[] = "`{$field}` = `$field` - $field_key";
        $this->bind_data[$field_key] = $value;
    }

    /**
     * 联合查询
     * @param $table
     * @param string $on
     * @param string $joinType
     * @return $this
     */
    public function join($table, $on = '', $joinType = 'left')
    {
        $this->join_option[] = [$joinType, $on, $table];
        return $this;
    }


    private function buildJoin(): string
    {
        if (!$this->join_str) {
            $join_str = "";
            foreach ($this->join_option as $join) {
                [$type, $on, $table] = $join;
                if (is_array($table)) {
                    $table = '`' . $this->parseTable($table[0]) . '`' . $table[1];
                } else {
                    $table = '`' . $this->parseTable($table) . '`';
                }
                $on = $on ? 'ON ' . $on : '';
                $join_str .= " {$type} JOIN {$table} {$on}";
            }
            $this->join_str = $join_str;
        }
        return $this->join_str;
    }

    /**
     * 设置查询条件
     * @param $field
     * @param null $op
     * @param null $condition
     * @return Query
     */
    public function where($field, $op = null, $condition = null)
    {
        return $this->parseWhereExp('AND', $field, $op, $condition);
    }

    public function having($condition = "")
    {
        $this->having = $condition;
    }

    /**
     * 设置查询条件
     * @param $field
     * @param null $op
     * @param null $condition
     * @return Query
     */
    public function whereOr($field, $op = null, $condition = null)
    {
        return $this->parseWhereExp('OR', $field, $op, $condition);
    }

    /**
     * @param string $logic
     * @param null $field
     * @param null $op
     * @param null $condition
     * @return $this
     */
    private function parseWhereExp($logic = 'AND', $field = null, $op = null, $condition = null)
    {
        if ($field) {
            $where = [];
            $logic = $this->has_where ? strtoupper($logic) : '';
            if (is_array($field)) {
                foreach ($field as $key => $value) {
                    if (is_array($value)) {
                        $where[] = sprintf('`%s` %s %s', $value[0], $value[1], $this->parseValue($value[2]));
                    } else {
                        $where[] = sprintf('`%s` = %s', $key, $this->parseValue($value));
                    }
                }
            } elseif (is_string($field) && is_string($op)) {
                $where[] = sprintf('`%s` %s %s', $field, $op, $this->parseValue($condition));
            } elseif (is_string($field) && is_array($op)) {
                $where[] = $field;
                $this->bind_data = array_merge($this->bind_data, $op);
            }
            $this->where[] = [$logic, implode(' AND ', $where)];
            $this->has_where = true;
        }
        return $this;
    }

    /**
     * 构建 where 条件字符串
     * @return string
     */
    private function buildWhere(): string
    {
        if ($this->where) {
            if (!$this->where_str) {
                $where_str = '';
                $where_count = count($this->where);
                $limit = false;
                if ($where_count > 1) {
                    $limit = true;
                }
                foreach ($this->where as $where) {
                    [$logic, $sub] = $where;
                    $where_str .= $logic . $this->parseSubWhere($sub, $limit);
                }
                $this->where_str = " WHERE " . $where_str;
            }
            return $this->where_str;
        }
        return "";
    }

    private function parseSubWhere(string $subWhere, $limit = true)
    {
        if (!$limit) {
            return $subWhere;
        }
        return ' (' . $subWhere . ') ';
    }

    /**
     * 解析值
     * @param $condition
     * @return string
     */
    private function parseValue($condition): string
    {
        if (is_array($condition)) {
            $in_value = [];
            $condition = array_unique($condition);
            foreach ($condition as $inValue) {
                $field_key = $this->getBindField();
                $in_value[] = $field_key;
                $this->bind_data[$field_key] = $inValue;
            }
            return '(' . implode(",", $in_value) . ')';
        } else if ($condition instanceof \Closure) {
            $query = new self($this->config);
            $query->setBindIndex($this->getBindIndex());
            $condition($query);
            $sql = $query->buildSql();
            $this->setBindIndex($query->getBindIndex());
            $this->bind_data = array_merge($this->bind_data, $query->getBindData());
            return $sql;
        } else {
            $field_key = $this->getBindField();
            $this->bind_data[$field_key] = $condition;
            return $field_key;
        }
    }

    public function data($data): Query
    {
        $this->update_data = $data;
        return $this;
    }


    /**
     * 构建SQL并且返回
     * @return string
     */
    public function buildSql(): string
    {
        return '(' . $this->buildSelectSql(false) . ')';
    }


    /**
     * 修改数据
     * @param $data
     * @return array|bool|mixed
     */
    public function update($data)
    {
        $fields = [];

        //处理单独传入的数据
        $data = array_merge($this->update_data, $data);
        foreach ($data as $key => $value) {
            $field_key = $this->getBindField();
            $fields[] = " `{$key}`= {$field_key}";
            $this->bind_data[$field_key] = $value;
        }

        if ($this->config['automatic_completion']) {
            $field_key = $this->getBindField();
            $fields[] = " `" . self::UPDATED_AT_FIELD . "` = {$field_key} ";
            $this->bind_data[$field_key] = time();

        }
        $fields = array_merge($this->incField, $fields);

        $field = implode(",", $fields);
        $table = $this->table;
        $alias = $this->alias;
        $join = $this->buildJoin();
        $where = $this->buildWhere();
        $order = $this->order;
        $limit = $this->limit;;

        $this->sql = "update `{$table}`{$alias} set {$field}{$join} {$where} {$order} {$limit}";
        //        var_dump($this->bindData);
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm, $this->bind_data);
    }


    /**
     * 插入数据
     * @param $data
     * @return int
     */
    public function insert($data = [])
    {
        $fields = '';
        $bind_data = [];
        $this->update_data = array_merge($this->update_data, $data);
        foreach ($this->update_data as $key => $value) {
            $field_key = $this->getBindField();
            $fields .= ",`{$key}` = {$field_key}";
            $bind_data[$field_key] = $value;
        }
        if ($this->config['automatic_completion']) {
            $field_key = $this->getBindField();
            $fields .= ",`" . self::UPDATED_AT_FIELD . "` = {$field_key}";
            $bind_data[$field_key] = time();

            $field_key = $this->getBindField();
            $fields .= ",`" . self::CREATED_AT_FIELD . "`={$field_key}";
            $bind_data[$field_key] = time();

        }
        $fields = substr($fields, 1);
        $this->sql = 'INSERT INTO `' . $this->table . '` SET ' . $fields;
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm, $bind_data);
    }

    /**
     * 插入数据并获取最后一条的自增ID
     * @param array $data
     * @return bool|string
     */
    function insertGetLastId($data = [])
    {
        if ($this->insert($data)) {
            return $this->getLastId();
        }
        return false;
    }

    /**
     * 插入数据
     * @param $data
     * @return int
     */
    public function insertAll($data)
    {
        $i = 0;
        $stm = null;
        try {
            $fields = array_keys($data[0]);
            $field_keys = [];
            $fieldArray = [];
            foreach ($fields as $key) {
                $fieldArray[] = "`{$key}`";
                $field_keys[$key] = $this->getBindField();;
            }
            if ($this->config['automatic_completion']) {
                $fieldArray[] = '`' . self::UPDATED_AT_FIELD . '`';
                $field_keys[self::UPDATED_AT_FIELD] = $this->getBindField();
                $fieldArray[] = '`' . self::CREATED_AT_FIELD . '`';
                $field_keys[self::CREATED_AT_FIELD] = $this->getBindField();
            }
            $this->sql = sprintf('INSERT INTO `%s` (%s) VALUES (%s)', $this->table, implode(",", $fieldArray), implode(",", $field_keys));
            $stm = self::$pdo->prepare($this->sql);
            foreach ($data as $item) {
                $bindData = [];
                foreach ($item as $key => $value) {
                    $bindData[$field_keys[$key]] = $value;
                }
                if ($this->config['automatic_completion']) {
                    $time = time();
                    $bindData[$field_keys[self::UPDATED_AT_FIELD]] = $time;
                    $bindData[$field_keys[self::CREATED_AT_FIELD]] = $time;
                }
                self::$pdo->execute($stm, $bindData);
                $i++;
            }
            return true;
        } catch (PDOException $exception) {
            return false;
        }
    }


    /**
     * 删除数据
     * @return array|bool|mixed
     */
    public function delete()
    {
        $this->sql = 'DELETE FROM `' . $this->table . '`' . $this->buildWhere() . $this->order . $this->limit;
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm, $this->bind_data);
    }

    /**
     * 添加字段
     * @param $field
     * @param $type
     * @param array $options
     * @return Query
     */
    public function addColumns($field, $type, $options = [])
    {
        $this->columns[$field] = ['type' => $type, 'options' => $options];
        return $this;
    }

    private function parseColumns($data, $option, $true, $false)
    {
        return isset($data[$option]) && $data[$option] ? sprintf(" " . $true . " ", $data[$option]) : $false;
    }

    /**
     * 创建新表
     * @param null $engine 使用的存储引擎
     * @param null $charset 设置默认的字符编码
     * @return array|bool|mixed
     */
    public function createTable($engine = null, $charset = null)
    {

        $engine = $engine ? 'ENGINE=' . $engine : '';
        $charset = $charset ? 'DEFAULT CHARSET=' . $charset : '';

        $sql = " CREATE TABLE IF NOT EXISTS `{$this->table}` (\r\n";
        $sqlc = [];
        foreach ($this->columns as $key => $column) {
            $field = $key;
            $options = $column["options"];
            $type = $column["type"];
            $length = $this->parseColumns($options, "length", "(%s)", "");
            $null = $this->parseColumns($options, "not_null", "NULL", " NOT NULL ");
            $primary_key = $this->parseColumns($options, "primary_key", "primary key", "");
            $auto_increment = $this->parseColumns($options, "auto_increment", "auto_increment", "");
            $sqlc[] = "\t`{$field}` {$type}{$length}{$null}{$primary_key}{$auto_increment}";
            //            $sql            .= implode(",\r\n", $sqlc);
        }
        $sql .= implode(",\r\n", $sqlc);
        $sql .= "\r\n)" . $engine . $charset;
        $this->sql = $sql;
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm);
    }

    /**
     * 删除当前表
     * @return string
     */
    public function dropTable()
    {
        $this->sql = 'drop table `' . $this->table . '`';
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm);
    }

    /**
     * 清空当前表 （会重置自增ID）
     * @return array|bool|mixed
     */
    public function truncate()
    {
        $this->sql = 'truncate table wp_comments `' . $this->table . '`';
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm);
    }

    /**
     * 重命名当前表
     * @param $newTableName
     * @return array|bool|mixed
     */
    public function rename($newTableName)
    {
        $this->sql = 'ALTER TABLE ' . $this->table . ' RENAME TO ' . $newTableName;
        $stm = self::$pdo->prepare($this->sql);
        return self::$pdo->execute($stm);
    }

    protected function buildSelectSql($isFind = false)
    {
        $field = $this->field;
        $table = $this->table;
        $alias = $this->alias;
        $join = $this->buildJoin();
        $where = $this->buildWhere();
        $group = $this->group;
        $having = $this->having;
        $order = $this->order;
        $limit = $isFind ? " LIMIT 1 " : $this->limit;
        $lock = $this->lock;
        return "SELECT " . $field . " FROM `" . $table . '`' . $alias . ' ' . $join . $where . $group . $having . $order . $limit . $lock;
    }

    /**
     * @param string $sql
     * @param array $data
     * @return bool|Collection
     */
    public function select($sql = null, $data = [])
    {
        if ($sql) {
            $this->sql = $sql;
            $this->bind_data = $data;
        } else {
            $this->sql = $this->buildSelectSql(false);
        }
        return $this->fetchAll($this->sql, $this->bind_data);
    }

    /**
     * @param string $sql
     * @param array $data
     * @return array|bool|Model
     */
    public function find($sql = null, $data = [])
    {
        if ($sql) {
            $this->sql = $sql;
            $this->bind_data = $data;
        } else {
            $this->sql = $this->buildSelectSql(true);
        }
        return $this->fetch($this->sql, $this->bind_data);
    }

    /**
     * @param $sql
     * @param $data
     * @return array|bool|Model
     */
    public function fetch($sql, $data)
    {
        $stm = self::$pdo->prepare($sql);
        $result = self::$pdo->find($stm, $data);
        if ($result && $this->model) {
            $this->model->setData($result);
            return $this->model;
        }
        return $result;
    }

    /**
     * @param $sql
     * @param null $data
     * @return Collection
     */
    public function fetchAll($sql, $data = null)
    {
        $stm = self::$pdo->prepare($sql);
        $result = self::$pdo->select($stm, $data);
        return $this->resultToCollection($result ?: []);
    }

    /**
     * @param $str
     * @return Query
     */
    public function field($str)
    {
        $zd = $str == null ? ' * ' : $str;
        $this->field = $zd;
        return $this;
    }

    /**
     * @param $limit
     * @param int $max
     * @return Paginate
     */
    public function paginate($limit, $max = 0)
    {
        $this->limit($max ?: false); //清除限制条件
        $rows = $this->count("*");
        $paginate = Paginate::container([
            'rows' => $rows,
            'pageRows' => $limit,
        ]);
        $this->limit($paginate->getStart(), $limit);
        $paginate->setData($this->select());
        return $paginate;
    }


    /**
     * @param string $field
     * @return int
     */
    public function count($field = '*')
    {
        $table = $this->table;
        $alias = $this->alias;
        $join = $this->buildJoin();
        $where = $this->buildWhere();
        $order = $this->order;
        $limit = $this->limit;
        $this->sql = "SELECT count(" . $field . ") as count FROM `" . $table . '`' . $alias . ' ' . $join . $where . $order . $limit;

        if ($num = $this->exec($this->sql, $this->bind_data)) {
            return $num['count'] ?: 0;
        }
        return 0;
    }

    /**
     * @return bool|Collection
     */
    public function showTables()
    {
        $sql = 'SHOW TABLES';
        $res = $this->select($sql);
        return $res;
    }

    /**
     * @return bool|Collection
     */
    public function showColumns()
    {
        $sql = "SHOW FULL COLUMNS FROM " . $this->table . "";
        $res = $this->select($sql);
        return $res;
    }

    /**
     * @param array $result
     * @param callable|null $callback
     * @return Collection
     */
    private function resultToCollection(array $result, callable $callback = null)
    {
        if ($this->model) {
            return $this->resultToModelCollection($result);
        }
        if ($this->index) {
            $result = array_column($result, $this->index);
        }
        return new Collection($result);
    }

    /**
     * @param array $result
     * @param callable|null $callback
     * @return Collection
     */
    private function resultToModelCollection(array $result)
    {
        $models = [];
        $relations = [];
        foreach ($this->with as $attr) {
            $method = toCamelCase($attr);
            /**
             * @var Relation $relationClass
             */
            $relationClass = $this->model->$method();
            $localKey = $relationClass->localKey;
            //执行关联查询的预处理方法
            $relationData = $relationClass->prepareRelation($result);
            $relations[toUnderScore($attr)] = [$localKey, $relationData];
        }

        foreach ($result as $item) {
            $cacheData = [];
            foreach ($relations as $attr => $r) {
                [$localKey, $relation] = $r;
                $key = $item[$localKey];
                if ($relation && $relation[$key]) {
                    $cacheData[$attr] = $relation[$key];
                } else {
                    $cacheData[$attr] = null;
                }
            }
            if ($this->index) {
                $pk = $item[$this->index];
                $models[$pk] = $this->model->newInstance($item, $cacheData);
            } else {
                $models[] = $this->model->newInstance($item, $cacheData);
            }

        }
        return new Collection($models, true);
    }

    /**
     * @param $sql
     * @param array $data
     * @return bool|int
     */
    public function exec($sql, $data = [])
    {
        $statement = self::$pdo->prepare($sql);
        return self::$pdo->execute($statement, $data);
    }


    public function getLastId()
    {
        return self::$pdo->pdo()->lastInsertId();
    }
}